﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Lib.Common;
using Microsoft.ApplicationBlocks.Data;
using Module.EntitiesInfo;

namespace Module.DataAccess
{
    public class QuestionDB
    {
        #region ConnectionString
        private string _connectionString;
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }
        public QuestionDB()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["localsql"].ToString();
        }
        #endregion
              
        public virtual void Delete(int id)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Id", id)
			};
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Questions_Delete", parameters);
        }
        public virtual int Insert(QuestionInfo info)
        {

            SqlParameter[] parameters = new SqlParameter[]
            {
				new SqlParameter("@Question", info.Question),
				new SqlParameter("@CategoryId", info.CategoryId),
				new SqlParameter("@Type", info.Type),
				new SqlParameter("@AnswerType", info.AnswerType),
				new SqlParameter("@Status", info.Status),
                new SqlParameter("@CorrectBitMask", info.CorrectBitMask),
                new SqlParameter("@CreatedDate", info.CreatedDate),
			};

            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Questions_Insert", parameters);
        }

        public virtual void Update(QuestionInfo info)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Id", info.Id),
                new SqlParameter("@Question", info.Question),
				new SqlParameter("@CategoryId", info.CategoryId),				
				new SqlParameter("@AnswerType", info.AnswerType),				
			};
            SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Questions_Update", parameters);
        }

        public virtual IDataReader GetInfo(int id)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Id", id)				
			};
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Questions_GetInfo", parameters);

        }
        public virtual IDataReader GetDataQuestion(int top)
        {
            string sql = "select top 3 * from Questions order by id desc";
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.Text,sql);

        }
        /// <summary>
        /// Tính tổng số bản ghi
        /// </summary>
        /// <param name="keysearch">Từ khóa tìm kiếm</param>
        /// <param name="categoryId">Thể loại câu hỏi</param>
        /// <param name="starDatetime">Ngày bắt đầu</param>
        /// <param name="endDatetime">Ngày kết thúc</param>
        /// <param name="beginRow">Bản ghi đầu tiền</param>
        /// <param name="endRow">Bản ghi cuối</param>
        /// <returns></returns>
        public virtual int GetCount(string keysearch, int categoryId, DateTime starDatetime, DateTime endDatetime, int beginRow, int endRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                          
               new SqlParameter("@keysearch", keysearch),  
               new SqlParameter("@CategoryId",categoryId),              
               new SqlParameter("@starDatetime", starDatetime),
               new SqlParameter("@endDatetime", endDatetime),
               new SqlParameter("@IsTotalRow", 1)
			};

            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Questions_Search", parameters);
        }

        /// <summary>
        /// Tìm kiếm
        /// </summary>
        /// <param name="keysearch">Từ khóa tìm kiếm</param>
        /// <param name="categoryId">Thể loại câu hỏi</param>
        /// <param name="starDatetime">Ngày bắt đầu</param>
        /// <param name="endDatetime">Ngày kết thúc</param>
        /// <param name="beginRow">Bản ghi đầu</param>
        /// <param name="endRow">Bản ghi cuối</param>
        /// <param name="orderByColumn">Sắp xếp</param>
        /// <returns></returns>
        public virtual DataTable Search(string keysearch, int categoryId, DateTime starDatetime, DateTime endDatetime, int beginRow, int endRow, string orderByColumn)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                
                new SqlParameter("@Keysearch", keysearch),  
                new SqlParameter("@CategoryId",categoryId), 
                new SqlParameter("@StarDatetime", starDatetime),
                new SqlParameter("@EndDatetime", endDatetime),               
                new SqlParameter("@BeginRow", beginRow),
                new SqlParameter("@EndRow", endRow),
                new SqlParameter("@OrderByColumn", orderByColumn),
				new SqlParameter("@IsTotalRow", 0)
			};

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "Questions_Search", parameters).Tables[0];
        }

       

      
        public virtual DataTable Select(int type, int values)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                
                new SqlParameter("@type", type),  
                new SqlParameter("@values", values)
			};

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "Questions_Select", parameters).Tables[0];
        }
    }
}

